using Dapper;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;

namespace Lzfy_His_Service.Repositories
{
    public class VerifyInfoRepository : IVerifyInfoRepository
    {
        private readonly IVerifyDbConnectionFactory _connectionFactory;

        public VerifyInfoRepository(IVerifyDbConnectionFactory connectionFactory)
        {
            _connectionFactory = connectionFactory;
        }

        public async Task<VerifyInfo?> GetByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                verify_code AS VerifyCode,
                qr_code AS QrCode,
                refund_balance AS RefundBalance,
                user_id AS UserId,
                user_name AS UserName,
                pat_name AS PatName,
                id_number AS IdNumber,
                deleted AS Deleted,
                deleted_time AS DeletedTime,
                deleted_by AS DeletedBy
                FROM lzfy_refund_verify_info WHERE id = @Id AND deleted = 0";
            
            return await connection.QueryFirstOrDefaultAsync<VerifyInfo>(sql, new { Id = id });
        }

        public async Task<IEnumerable<VerifyInfo>> GetAllAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                verify_code AS VerifyCode,
                qr_code AS QrCode,
                refund_balance AS RefundBalance,
                user_id AS UserId,
                user_name AS UserName,
                pat_name AS PatName,
                id_number AS IdNumber,
                deleted AS Deleted,
                deleted_time AS DeletedTime,
                deleted_by AS DeletedBy
                FROM lzfy_refund_verify_info WHERE deleted = 0 ORDER BY input_date DESC";
            
            return await connection.QueryAsync<VerifyInfo>(sql);
        }

        public async Task<IEnumerable<VerifyInfo>> GetByPatientIdAsync(string patientId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                verify_code AS VerifyCode,
                qr_code AS QrCode,
                refund_balance AS RefundBalance,
                user_id AS UserId,
                user_name AS UserName,
                pat_name AS PatName,
                id_number AS IdNumber,
                deleted AS Deleted,
                deleted_time AS DeletedTime,
                deleted_by AS DeletedBy
                FROM lzfy_refund_verify_info WHERE patient_id = @PatientId AND deleted = 0 ORDER BY input_date DESC";
            
            return await connection.QueryAsync<VerifyInfo>(sql, new { PatientId = patientId });
        }

        public async Task<VerifyInfo?> GetByVerifyCodeAsync(string verifyCode)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                verify_code AS VerifyCode,
                qr_code AS QrCode,
                refund_balance AS RefundBalance,
                user_id AS UserId,
                user_name AS UserName,
                pat_name AS PatName,
                id_number AS IdNumber,
                deleted AS Deleted,
                deleted_time AS DeletedTime,
                deleted_by AS DeletedBy
                FROM lzfy_refund_verify_info WHERE verify_code = @VerifyCode AND deleted = 0";
            
            return await connection.QueryFirstOrDefaultAsync<VerifyInfo>(sql, new { VerifyCode = verifyCode });
        }

        public async Task<VerifyInfo?> GetByQrCodeAsync(string qrCode)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                verify_code AS VerifyCode,
                qr_code AS QrCode,
                refund_balance AS RefundBalance,
                user_id AS UserId,
                user_name AS UserName,
                pat_name AS PatName,
                id_number AS IdNumber,
                deleted AS Deleted,
                deleted_time AS DeletedTime,
                deleted_by AS DeletedBy
                FROM lzfy_refund_verify_info WHERE qr_code = @QrCode AND deleted = 0";
            
            return await connection.QueryFirstOrDefaultAsync<VerifyInfo>(sql, new { QrCode = qrCode });
        }

        public async Task<IEnumerable<VerifyInfo>> GetRecentAsync(int minutes, string? userId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>
            {
                "input_date >= DATEADD(MINUTE, -@Minutes, GETDATE())",
                "deleted = 0" // 只查询未删除的记录
            };
            
            var parameters = new DynamicParameters();
            parameters.Add("Minutes", minutes);
            
            if (!string.IsNullOrEmpty(userId))
            {
                whereConditions.Add("user_id = @UserId");
                parameters.Add("UserId", userId);
            }
            
            var whereClause = "WHERE " + string.Join(" AND ", whereConditions);
            
            var sql = $@"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                verify_code AS VerifyCode,
                qr_code AS QrCode,
                refund_balance AS RefundBalance,
                user_id AS UserId,
                user_name AS UserName,
                pat_name AS PatName,
                id_number AS IdNumber,
                deleted AS Deleted,
                deleted_time AS DeletedTime,
                deleted_by AS DeletedBy
                FROM lzfy_refund_verify_info 
                {whereClause}
                ORDER BY input_date DESC";
            
            return await connection.QueryAsync<VerifyInfo>(sql, parameters);
        }

        public async Task<(IEnumerable<VerifyInfo> data, int totalCount)> GetWithFilterAsync(
            string? patientId = null,
            string? verifyCode = null,
            string? userId = null,
            string? patName = null,
            string? idNumber = null,
            DateTime? startDate = null,
            DateTime? endDate = null,
            int pageIndex = 1,
            int pageSize = 20)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string> { "deleted = 0" };
            var parameters = new DynamicParameters();

            if (!string.IsNullOrEmpty(patientId))
            {
                whereConditions.Add("patient_id = @PatientId");
                parameters.Add("PatientId", patientId);
            }

            if (!string.IsNullOrEmpty(verifyCode))
            {
                whereConditions.Add("verify_code = @VerifyCode");
                parameters.Add("VerifyCode", verifyCode);
            }

            if (!string.IsNullOrEmpty(userId))
            {
                whereConditions.Add("user_id = @UserId");
                parameters.Add("UserId", userId);
            }

            if (!string.IsNullOrEmpty(patName))
            {
                whereConditions.Add("pat_name LIKE @PatName");
                parameters.Add("PatName", $"%{patName}%");
            }

            if (!string.IsNullOrEmpty(idNumber))
            {
                whereConditions.Add("id_number = @IdNumber");
                parameters.Add("IdNumber", idNumber);
            }

            if (startDate.HasValue)
            {
                whereConditions.Add("input_date >= @StartDate");
                parameters.Add("StartDate", startDate.Value);
            }

            if (endDate.HasValue)
            {
                whereConditions.Add("input_date <= @EndDate");
                parameters.Add("EndDate", endDate.Value.AddDays(1).AddSeconds(-1));
            }

            var whereClause = "WHERE " + string.Join(" AND ", whereConditions);

            // 获取总数
            var countSql = $"SELECT COUNT(1) FROM lzfy_refund_verify_info {whereClause}";
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

            // 获取分页数据
            var offset = (pageIndex - 1) * pageSize;
            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);

            var sql = @"SELECT 
                id AS Id,
                input_date AS InputDate,
                patient_id AS PatientId,
                verify_code AS VerifyCode,
                qr_code AS QrCode,
                refund_balance AS RefundBalance,
                user_id AS UserId,
                user_name AS UserName,
                pat_name AS PatName,
                id_number AS IdNumber
                FROM lzfy_refund_verify_info {whereClause}
                ORDER BY input_date DESC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            var data = await connection.QueryAsync<VerifyInfo>(sql, parameters);

            return (data, totalCount);
        }

        public async Task<int> CreateAsync(VerifyInfo verifyInfo)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"INSERT INTO lzfy_refund_verify_info 
                (input_date, patient_id, verify_code, qr_code, refund_balance, user_id, user_name, pat_name, id_number, deleted, deleted_time, deleted_by)
                VALUES (@InputDate, @PatientId, @VerifyCode, @QrCode, @RefundBalance, @UserId, @UserName, @PatName, @IdNumber, @Deleted, @DeletedTime, @DeletedBy);
                SELECT CAST(SCOPE_IDENTITY() as int);";
            
            return await connection.QuerySingleAsync<int>(sql, new
            {
                InputDate = verifyInfo.InputDate ?? DateTime.Now,
                PatientId = verifyInfo.PatientId,
                VerifyCode = verifyInfo.VerifyCode,
                QrCode = verifyInfo.QrCode,
                RefundBalance = verifyInfo.RefundBalance,
                UserId = verifyInfo.UserId,
                UserName = verifyInfo.UserName,
                PatName = verifyInfo.PatName,
                IdNumber = verifyInfo.IdNumber,
                Deleted = false, // 新创建的记录默认未删除
                DeletedTime = (DateTime?)null, // 新创建的记录删除时间为空
                DeletedBy = (string?)null // 新创建的记录删除者为空
            });
        }

        public async Task<bool> UpdateAsync(VerifyInfo verifyInfo)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"UPDATE lzfy_refund_verify_info SET 
                input_date = @InputDate,
                patient_id = @PatientId,
                verify_code = @VerifyCode,
                qr_code = @QrCode,
                refund_balance = @RefundBalance,
                user_id = @UserId,
                user_name = @UserName,
                pat_name = @PatName,
                id_number = @IdNumber,
                deleted = @Deleted,
                deleted_time = @DeletedTime,
                deleted_by = @DeletedBy
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, new
            {
                Id = verifyInfo.Id,
                PatientId = verifyInfo.PatientId,
                VerifyCode = verifyInfo.VerifyCode,
                QrCode = verifyInfo.QrCode,
                RefundBalance = verifyInfo.RefundBalance,
                UserId = verifyInfo.UserId,
                UserName = verifyInfo.UserName,
                PatName = verifyInfo.PatName,
                IdNumber = verifyInfo.IdNumber
            });
            
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "DELETE FROM lzfy_refund_verify_info WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }
        
        public async Task<bool> SoftDeleteAsync(int id, string deletedBy)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"UPDATE lzfy_refund_verify_info SET 
                deleted = 1,
                deleted_time = @DeletedTime,
                deleted_by = @DeletedBy
                WHERE id = @Id AND deleted = 0";
            
            var rowsAffected = await connection.ExecuteAsync(sql, new { 
                Id = id, 
                DeletedTime = DateTime.Now, 
                DeletedBy = deletedBy 
            });
            return rowsAffected > 0;
        }
    }
}